<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

/**
 * This is reports model
 * There are no params, it's main model class
 * If you want to participate on our project, just write to adamseidel@gamil.com or use seidel.ad@gmail.com+
 * or the third one: adamko.seidelko.bugfinderko@soferujem.sk
 */
class Reports_model extends CI_Model {

    var $type = 1;
    var $limit = 20;
    var $keywords = 0;

    public function __construct() {
        parent::__construct();
        $this->load->database();
    }

    public function fetchReports() {
        $query = $this->db->get('reports');
        return $query->result_array();
    }

    public function fetchLast1000Reports($date_from, $date_to) {
        $sql = "select seen,type_id,city,latitude,longitude,count(*) as pocet
		 from reports where DATE(seen)>='$date_from' AND DATE(seen)<='$date_to'
		 	group by street,city,type_id order by pocet";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    private function userAreas($user_id) {
        $sql = "SELECT areas from users where user_id='$user_id'";
        $query = $this->db->query($sql);
        return $query->row_array();
    }

    private function userKeywords($user_id) {
        $sql = "SELECT keywords from users where user_id='$user_id'";
        $query = $this->db->query($sql);
        return $query->row_array();
    }

    public function fetchLast25() {
        $user_id = $this->session->userdata("sess_user_id");
        if (!empty($user_id)) {
            $ar = $this->userAreas($user_id);
            $areas_in = " AND r.area_id IN(" . $ar['areas'] . ")";

            $keyw = $this->userKeywords($user_id);
            if (!empty($keyw['keywords'])) { // len ak su keywordy rob...
                $pole = explode(",", $keyw['keywords']);
                $places_keyw = "OR (";

                foreach ($pole as $key) {

                    if (($this->keywords) == 0)
                        $places_keyw .= "street LIKE '%";
                    else
                        $places_keyw .= " OR street LIKE '%";
                    $places_keyw .= $key;
                    $places_keyw .= "%' OR city LIKE '%";
                    $places_keyw .= $key;
                    $places_keyw .= "%' OR state LIKE '%";
                    $places_keyw .= $key;
                    $places_keyw .= "%'";
                    $this->keywords++;
                }
                $places_keyw .= ")";
            }
            else {
                $places_keyw = "";
            }
        } else {
            $areas_in = "";
            $places_keyw = "";
        }

        $sql = "SELECT r.*,a.name from reports as r JOIN areas as a using(area_id) where r.type_id IN (1,2,3,4) $areas_in $places_keyw ORDER BY r.report_id DESC LIMIT 25";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function fetchToJson($type) {

        $user_id = $this->session->userdata("sess_user_id");
        if (!empty($user_id)) {
            $ar = $this->userAreas($user_id);
            $areas_in = " AND area_id IN(" . $ar['areas'] . ")";

            $keyw = $this->userKeywords($user_id);
            if (!empty($keyw['keywords'])) { // len ak su keywordy rob...
                $pole = explode(",", $keyw['keywords']);
                $places_keyw = "OR (";

                foreach ($pole as $key) {

                    if (($this->keywords) == 0)
                        $places_keyw .= "street LIKE '%";
                    else
                        $places_keyw .= " OR street LIKE '%";
                    $places_keyw .= $key;
                    $places_keyw .= "%' OR city LIKE '%";
                    $places_keyw .= $key;
                    $places_keyw .= "%' OR state LIKE '%";
                    $places_keyw .= $key;
                    $places_keyw .= "%'";
                    $this->keywords++;
                }
                $places_keyw .= ")";
            }
            else {
                $places_keyw = "";
            }
        } else {
            $areas_in = "";
            $places_keyw = "";
        }

        if ($type == 0) {
            $sql = "SELECT * from reports where type_id IN (1,2,3,4) $areas_in $places_keyw ORDER BY seen DESC LIMIT 50";
            $query = $this->db->query($sql);
            return $query->result_array();
        } else {
            $sql = "SELECT * from reports where type_id='$type' $areas_in $places_keyw ORDER BY seen DESC LIMIT 15";
            $query = $this->db->query($sql);
            return $query->result_array();
        }
    }

    public function fetchToGraph($date) {
        $oneDayAgo = date("Y-m-d", strtotime("$date -1 days"));
        $sixDaysAgo = date("Y-m-d", strtotime("$date -10 days"));
        $sql = "SELECT DATE(seen) as datum,count(type_id) as pocet FROM reports WHERE 
		type_id IN (1,2,3,4) AND DATE(seen)>'$sixDaysAgo' 
		AND DATE(seen)<='$oneDayAgo' 
		AND (state='Slovenská republika' OR state='Slovakia') group by DATE(seen)";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function getAllReports() {
        $sql = "SELECT type_id,sum(type_id) from reports GROUP BY type_id";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function countAllReports() {
        $sql = "SELECT * from reports";
        $query = $this->db->query($sql);
        return $query->num_rows();
    }

    public function fetchReportByCity($id) {
        $sql = " SELECT *,a.name from reports join areas as a using(area_id) where report_id='$id'";
        $query = $this->db->query($sql);
        return $query->row_array();
    }

    public function fetchReportLatLongRounded($lat, $lng) {
        $sql = " SELECT * from reports
						 where 
						 	latitude LIKE '$lat%' and 
						 	longitude LIKE '$lng%' 
						 		order by seen 
						 			desc LIMIT 1";
        $query = $this->db->query($sql);
        return $query->row_array();
    }

    public function fetchLastReportId() {
        $sql = "SELECT report_id from reports order by report_id desc LIMIT 1";
        $query = $this->db->query($sql);
        return $query->row_array();
    }

    public function fetchReportsNewerThanId($id) {
        $sql = "select count(report_id) as count from reports where report_id>'$id'";
        $query = $this->db->query($sql);
        return $query->row_array();
    }

    public function fetchReportLatLong($lat, $lng, $date_from, $date_to, $type_id) {

        $sql = "SELECT count(report_id) as coun,
			    			   avg(latitude) as avglat,
			    			   avg(longitude) as avglng, 
			    			   type_id 
			    			   	from reports 
			    			   		where Date(seen)>='$date_from' AND 
			    			   			  Date(seen)<='$date_to' AND 
			    			   			  latitude like '$lat%' AND
			    			   			  longitude like '$lng%' AND 
			    			   			  type_id='$type_id'";

        $query = $this->db->query($sql);
        return $query->row_array();
    }

    public function lolo() {
        $sql = "select Date(seen) as d_seen,type_id,round(latitude,3) as lat,round(longitude,3) as lng,count(*) as cc 
		from reports group by round(latitude,2),round(longitude,2),Date(seen),type_id order by d_seen,cc";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function fetchAllCountries() {
        $sql = "select state,count(*) as pocet from reports group by state order by pocet desc";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function fetchLastReportOccurences($city, $street, $not_id) {
        $sql = "SELECT * from reports where street='$street' AND city='$city' AND report_id!='$not_id' order by seen DESC LIMIT 5";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function fetchLastReportOccurencesCount($city, $street, $not_id) {
        $sql = "SELECT count(*) as count from reports where street='$street' AND city='$city' AND report_id!='$not_id'";
        $query = $this->db->query($sql);
        return $query->row_array();
    }

    // +1 comment to n_comments in reports table
    public function plusOneComment($id) {
        $sql = "UPDATE reports SET n_comments=n_comments+1 WHERE report_id='$id'";
        $this->db->query($sql);
    }

    public function plusOneThumb($id, $decision) {
        switch ($decision) {
            case 'up':
                $row = 'n_thumb_ups';
                break;

            default:
                $row = 'n_thumb_downs';
                break;
        }
        $sql = "UPDATE reports SET $row=$row+1 WHERE report_id='$id'";
        $this->db->query($sql);
    }

    public function insert($user_id, $type_id, $latitude, $longitude, $street, $city, $state, $street_noaccent, $city_noaccent, $seen, $kraj_id) {
        if (isset($user_id) && isset($type_id) && isset($latitude) && isset($longitude) && isset($street) && isset($city) && isset($state) && isset($street_noaccent) && isset($city_noaccent) && isset($seen)) {
            $added = date('Y-m-d H:i:s');
            $seen = date('Y-m-d H:i:s', strtotime($seen));
            $sql = "INSERT into reports (user_id,type_id,area_id,
				latitude,longitude,street,
				city,state,street_noaccent,
				city_noaccent,seen,added) 
					VALUES('$user_id','$type_id','$kraj_id',
						'$latitude','$longitude','" . $street . "',
						'" . $city . "','" . $state . "','" . $street_noaccent . "',
						'" . $city_noaccent . "','" . $seen . "','" . $added . "')";
            $query = $this->db->query($sql);
        }
    }

    public function updateRow($type_row, $user_id) {// update row after adding report
        $sql = "UPDATE users SET $type_row=$type_row+1 WHERE user_id='$user_id'";
        $this->db->query($sql);
    }

    public function reportreport($user_id, $report_id) {

        $date = date('Y-m-d H:i:s');

        $sql = "UPDATE reports SET reported=reported+1 WHERE report_id='$report_id'";
        $query = $this->db->query($sql);

        $sql = "INSERT into reported_reports VALUES('','$user_id','$report_id','$date')";
        $query = $this->db->query($sql);
    }

    public function checkOccurences($value) {
        $sql = "SELECT count(report_id) as pocet from reports 
		where 	street LIKE '%$value%' OR 
		 		city LIKE '%$value%' OR 
		  		state LIKE '%$value%'";
        $query = $this->db->query($sql);
        return $query->row_array();
    }

    public function searchResult($post) {

        $comm = $this->doSelectComment($post['komenty']);
        $thumb = $this->doSelectThumb($post['thumby']);

        $areas = $this->getForIN($post['area']);
        $types = $this->getForIN($post['typy']);
        $start_date = date('Y-m-d', strtotime($post['date1']));
        $start_time = date('H:i:s', strtotime($post['time1']));
        $end_date = date('Y-m-d', strtotime($post['date2']));
        $end_time = date('H:i:s', strtotime($post['time2']));
        $limit = $post['limit'];
        $sql = "SELECT * from reports 
             	where type_id in ($types) and 
             	area_id in ($areas) and 
             	TIME(seen) >= '$start_time' and 
             	TIME(seen) <= '$end_time' and 
             	DATE(seen) >= '$start_date' and 
             	DATE(seen) <= '$end_date' $comm $thumb
             		order by seen 
             			limit $limit";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    private function getForIN($inp) {
        $string = "";
        foreach ($inp as $key) {
            $string .= $key . ',';
        }
        return substr($string, 0, strlen($string) - 1);
    }

    private function doSelectComment($comm) {
        switch ($comm) {
            case 'comment1':
                $insert = " and n_comments>0 ";
                break;
            case 'comment2':
                $insert = " and n_comments=0 ";
                break;
            case 'comment3': //
                $insert = "";
                break;
            default:
                $insert = "";
                break;
        }
        return $insert;
    }

    private function doSelectThumb($thumb) {
        switch ($thumb) {
            case 'thumb1':
                $insert = " and n_thumb_ups>0 and n_thumb_downs=0 ";
                break;
            case 'thumb2':
                $insert = " and n_thumb_ups=0 and n_thumb_downs>0 ";
                break;
            case 'thumb3':
                $insert = " and n_thumb_ups>n_thumb_downs ";
                break;
            case 'thumb4':
                $insert = "";
                break;
            default:
                $insert = "";
                break;
        }
        return $insert;
    }

}
